PHP – Dynamic Drop-down Box for MySQL DB
Summary
Here is a “quick and dirty” way to get a drop-down box in an HTML page to show the latest values from a MySQL database. Notice that there is a significant lack of error-handling. It sure did help clean up my HTML form, though.
Assumptions: You already have a mysql connection object open and a MySQL database selected. I now have a MySQL Database Connection Function available. By default, the mysql_query function will use the latest values. Here is a function that you can separate out as an include file and call as needed. I don’t know if the starting and ending php tags are needed, but it works and makes it much easier to edit in Geany.
<?php
function myDropdown($intIdField, $strNameField, $strTableName, $strNameOrdinal, $strMaskName, $strOrderField, $strMethod="asc") {
//
// PHP DYNAMIC DROP-DOWN BOX - HTML SELECT
//
// 2012-10 http://kimbriggs.com
//
// Function creates a drop-down box
// by dynamically querying ID-Name pair from a lookup table.
//
// Parameters:
// intIdField = Integer "ID" field of table, usually the primary key.
// strMaskName = What shows up first in the drop-down box.
// strMethod = Sort as asc=ascending (default) or desc for descending.
// strNameField = Name field that user picks as a value.
// strNameOrdinal = $_POST name handles multiple drop-downs.
// strOrderField = Which field you want results sorted by.
// strTableName = MySQL table containing intIDField and strNameField.
//
// Returns:
// HTML Drop-Down Box Mark-up Code
//
echo "<select name=\"$strNameOrdinal\">\n";
echo "<option value=\"NULL\">".$strMaskName."</option>\n";
$strQuery = "select $intIdField, $strNameField
from $strTableName
order by $strOrderField $strMethod";
$rsrcResult = mysql_query($strQuery);
while($arrayRow = mysql_fetch_assoc($rsrcResult)) {
$strA = $arrayRow["$intIdField"];
$strB = $arrayRow["$strNameField"];
echo "<option value=\"$strA\">$strB</option>\n";
}
echo "</select>";
}
?>And here are examples of how it looks when used in a script: Include a file that contains the code in the head section. Mine is in file called “lib.inc” within the main include directory specified by “include_path” in my php.ini file.
<head>
<title>My Title</title>
<?php require_once("lib.inc") ?>
</head>Within PHP, just call the function and its arguments. Here is an example for a State Table.
<?php
$intIdField = 'state_id';
$strNameField = 'state_name';
$strTableName = 'tbl_states';
$strNameOrdinal = 'state_id_mg';
$strMaskName = 'Select State';
$strOrderField = 'state_name';
...
myDropdown($intIdField, $strNameField, $strTableName, $strNameOrdinal, $strMaskName, $strOrderField, $strMethod="asc");
?>